﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Text.RegularExpressions;
namespace I1
{
    public partial class Form1 : Form
    {
        private string theChinese;
        private string theEnglish;
        public Form1()
        {
            //createTable();用C# 创建sql server 数据库，此函数用于创建word表，最开始运行一次
            //
            //DBwriter(); 此函数用于为Word表增加数据，从txt文件中导入
            InitializeComponent();
            getWord();//随机得到一个单词
            chinese.Text = theChinese;
            rightor.Text = "思考ing";
            answer.Text = "";
            english.Text = "";
        }
        private void getWord()
        {
            SqlConnection mySqlConnection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\code\database\sql.mdf;Integrated Security=True;Connect Timeout=30");
            mySqlConnection.Open();
            using (SqlCommand myCommand = new SqlCommand())
            {
                myCommand.Connection = mySqlConnection;
                myCommand.CommandText = "SELECT * FROM word";

                using (SqlDataReader reader = myCommand.ExecuteReader())
                {
                    Random random = new Random();
                    int num=random.Next(1,600);
                    for(int i = 0; i < num; i++)
                    {
                        reader.Read();
                    }
                    theEnglish = reader.GetString(0);
                    theChinese = reader.GetString(1);
                }
                //myCommand.ExecuteNonQuery();
            }

            mySqlConnection.Close();
        }
        private void DBreader()
        {
            SqlConnection mySqlConnection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\code\database\sql.mdf;Integrated Security=True;Connect Timeout=30");
            mySqlConnection.Open();
            using (SqlCommand myCommand = new SqlCommand())
            {
                myCommand.Connection = mySqlConnection;
                myCommand.CommandText = "SELECT * FROM word";

                using (SqlDataReader reader = myCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Debug.WriteLine(reader.GetString(0) + ": "
                           + reader.GetString(1));
                    }
                }
                //myCommand.ExecuteNonQuery();
            }

            mySqlConnection.Close();
        }
        private void createTable()
        {
            SqlConnection mySqlConnection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\code\database\sql.mdf;Integrated Security=True;Connect Timeout=30");
            mySqlConnection.Open();
            using (SqlCommand myCommand = new SqlCommand())
            {
                myCommand.Connection = mySqlConnection;
                myCommand.CommandText = "create table word(English VARCHAR(20),Chinese NVARCHAR(30))";
                myCommand.ExecuteNonQuery();               
            }
            mySqlConnection.Close();
        }
        private void txtReader()
        {
            //'D:\code\c_shap\I1\bin\Debug\netcoreapp3.1\word.txt'.”
            using (StreamReader sr = new StreamReader(@"..\..\..\word.txt", Encoding.UTF8))
            {
                string nextLine;
                string Eparten = @"[a-zA-Z]+";
                string Cparten = @"[a-z][.]\s{0,2}[\u0020\u3000\u4e00-\u9fa5,，；v.ntia;（）、]+";
                while ((nextLine = sr.ReadLine()) != null)
                {
                    string english = Regex.Match(nextLine, Eparten).ToString();
                    string chinese = Regex.Match(nextLine, Cparten).ToString();
                    Debug.WriteLine(english+":"+ chinese);
                }
            }
        }
        private void DBwriter()
        {
            SqlConnection mySqlConnection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\code\database\sql.mdf;Integrated Security=True;Connect Timeout=30");
            mySqlConnection.Open();
            using (StreamReader sr = new StreamReader(@"..\..\..\word.txt", Encoding.UTF8))
            {
                string nextLine;
                string Eparten = @"[a-zA-Z]+";
                string Cparten = @"[a-z][.]\s{0,2}[\u0020\u3000\u4e00-\u9fa5,，；v.ntia;（）、]+";

                using (SqlCommand myCommand = new SqlCommand())
                {
                    myCommand.Connection = mySqlConnection;
                    //myCommand.CommandText = "create table buhao(No VARCHAR(8),Name VARCHAR(8),Sex NVARCHAR(8))";
                    // myCommand.CommandText = "insert into buhao(No,Name,Sex)values(@No,@Name,@Sex)";
                    

                    while ((nextLine = sr.ReadLine()) != null)
                    {
                        myCommand.CommandText = "insert into word(English,Chinese)values(@English1,@Chinese1)";
                        string english = Regex.Match(nextLine, Eparten).ToString();
                        string chinese = Regex.Match(nextLine, Cparten).ToString();
                        myCommand.Parameters.AddWithValue("@English1", english);
                        myCommand.Parameters.AddWithValue("@Chinese1", chinese);
                        myCommand.ExecuteNonQuery();
                        myCommand.Parameters.Clear();
                        //Debug.WriteLine(english + ":" + chinese);
                    }
                    
                }
                
            }
            

            mySqlConnection.Close();
        }

        private void confirm()
        {
            if (theEnglish == null) return;
            if (theEnglish.ToLower() == english.Text.ToLower())
            {
                rightor.Text = "right";

            }
            else
            {
                rightor.Text = "wrong";

            }
            answer.Text = theEnglish;
            Debug.WriteLine(theEnglish);

        }
        private void delete()
        {
            SqlConnection mySqlConnection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\code\database\sql.mdf;Integrated Security=True;Connect Timeout=30");
            mySqlConnection.Open();
            using (SqlCommand myCommand = new SqlCommand())
            {
                myCommand.Connection = mySqlConnection;
                myCommand.CommandText = "drop table word";

                
                myCommand.ExecuteNonQuery();
            }

            mySqlConnection.Close();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            confirm();
        }

        private void nextone_Click(object sender, EventArgs e)
        {
            getWord();
            chinese.Text = theChinese;
            rightor.Text = "思考ing";
            answer.Text = "";
            english.Text = "";
        }

        private void Form1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                confirm();
            }
        }
    }
}
